import pymysql



class DButils:
    def __init__(self,host,user,password,database,port=3306,charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset


    def upgrade(self,sql,data):
        db = pymysql.connect(
            host=self.host,
            user = self.user,
            password = self.password,
            database = self.database,
            port = self.port,
            charset = self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                db.commit()
                print('数据操作成功！')
        except Exception as e:
            print('数据操作失败！', e)
        finally:
            db.close()

    def many_upgrade(self, sql, data_list):
        """批量执行 SQL 语句（如批量插入）"""
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                # 使用 executemany 批量执行
                cursor.executemany(sql, data_list)
                db.commit()
                print(f"批量操作成功，影响行数: {cursor.rowcount}")
        except Exception as e:
            print(f"批量操作失败: {e}")
            db.rollback()
        finally:
            db.close()

    def select(self,sql,data):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                result2 = cursor.fetchall()
                print('数据查询成功！')
                return result2
        except Exception as e:
            print('数据操作失败！', e)

        finally:
            db.close()




db = DButils('127.0.0.1','root','root','finance')
sql = "select * from user where username =%s"
data = ['lisi']
result1 = db.select(sql,data)
print(result1)
# print("请依次分别输入10个人信息:")
#
# for i in range(10):
#     flag = input('按E可退出操作！按I继续操作！:')
#     if flag.upper() == 'E':
#         print("退出成功！")
#         break
#     elif flag.upper() == 'I':
#         name = input(f'请输入第{i + 1}个人姓名:')
#         age = int(input(f'请输入第{i + 1}个人年龄:'))
#         sex = input(f'请输入第{i + 1}个人性别:')
#         high = float(input(f'请输入第{i + 1}个人身高（如1.75）:'))
#         sql = "insert into person values (%s,%s,%s,%s)"
#         data = (name, age, sex, high)
#         db.upgrade(sql, data)
#     else:
#         print('输入字符无效！')


